<?php
/**
 * excel 转数组
 *
 * @param $filename
 * @return array
 * @throws PHPExcel_Reader_Exception
 */
if (!function_exists('fish_excel_to_arr_h')) {
    function fish_excel_to_arr_h($filename)
    {
        require_once __DIR__ . '/Classes/PHPExcel/IOFactory.php';
        //加载excel文件
        $objPHPExcelReader = PHPExcel_IOFactory::load($filename);

        $sheet = $objPHPExcelReader->getSheet(0);        // 读取第一个工作表(编号从 0 开始)
        $highestRow = $sheet->getHighestRow();            // 取得总行数
        $highestColumn = $sheet->getHighestColumn();    // 取得总列数

        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        // 一次读取一列
        $res_arr = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0; $arr[$column] != 'F'; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] = $val;
            }

            $res_arr[] = $row_arr;
        }
        return $res_arr;
    }
}

/**
 * excel 转数组
 *
 * @param $filename
 * @return array
 * @throws PHPExcel_Reader_Exception
 */
if (!function_exists('fish_excel_to_arr_s')) {
    function fish_excel_to_arr_s($filename)
    {
        require_once __DIR__ . '/Classes/PHPExcel/IOFactory.php';
        $objPHPExcelReader = PHPExcel_IOFactory::load($filename);
        $reader = $objPHPExcelReader->getWorksheetIterator();
        //循环读取sheet
        foreach ($reader as $sheet) {
            //读取表内容
            $content = $sheet->getRowIterator();
            //逐行处理
            $res_arr = array();
            foreach ($content as $key => $items) {
                $rows = $items->getRowIndex();                //行
                $columns = $items->getCellIterator();        //列
                $row_arr = array();
                //确定从哪一行开始读取
                if ($rows < 2) {
                    continue;
                }
                //逐列读取
                foreach ($columns as $head => $cell) {
                    //获取cell中数据
                    $data = $cell->getValue();
                    $row_arr[] = $data;
                }
                $res_arr[] = $row_arr;
            }
        }
        foreach ($res_arr as &$item) {
            $item = array_filter($item);
        }
        $res_arr = array_filter($res_arr);
        return $res_arr;
    }
}

/**
 * 下载excel
 *
 * @param $list
 * @param $filename
 * @param array $indexKey
 * @throws PHPExcel_Exception
 * @throws PHPExcel_Reader_Exception
 * @throws PHPExcel_Writer_Exception
 */
if (!function_exists('fish_export_excel')) {
    function fish_export_excel($filePath, $data)
    {
        require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
        require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
        require_once dirname(__FILE__) . '/Classes/PHPExcel/Writer/Excel2007.php';
        $pathInfo = pathinfo($filePath);
        $obj = new \PHPExcel();
        // 以下内容是excel文件的信息描述信息
        $obj->getProperties()->setCreator(''); //设置创建者
        $obj->getProperties()->setLastModifiedBy(''); //设置修改者
        $obj->getProperties()->setTitle(''); //设置标题
        $obj->getProperties()->setSubject(''); //设置主题
        $obj->getProperties()->setDescription(''); //设置描述
        $obj->getProperties()->setKeywords('');//设置关键词
        $obj->getProperties()->setCategory('');//设置类型

        // 设置当前sheet
        $obj->setActiveSheetIndex(0);

        // 设置当前sheet的名称
        $obj->getActiveSheet()->setTitle('student');

        // 列标
        $list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

        // 填充第一行数据
        $obj->getActiveSheet()
            ->setCellValue($list[0] . '1', '学号')
            ->setCellValue($list[1] . '1', '姓名')
            ->setCellValue($list[2] . '1', '班级');

        // 填充第n(n>=2, n∈N*)行数据
        $length = count($data);
        for ($i = 0; $i < $length; $i++) {
            $obj->getActiveSheet()->setCellValue($list[0] . ($i + 2), '20190101', \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
            $obj->getActiveSheet()->setCellValue($list[1] . ($i + 2), 'student01');
            $obj->getActiveSheet()->setCellValue($list[2] . ($i + 2), '1班');
        }

        // 设置加粗和左对齐
        foreach ($list as $col) {
            // 设置第一行加粗
            $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
            // 设置第1-n行，左对齐
            for ($i = 1; $i <= $length + 1; $i++) {
                $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            }
        }

        // 设置列宽
        $obj->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $obj->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $obj->getActiveSheet()->getColumnDimension('C')->setWidth(15);

        // 导出
        ob_clean();
        if ($pathInfo['extension'] === "xls") {
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $pathInfo['filename'] . '.xls');
            header('Cache-Control: max-age=1');
            $objWriter = new \PHPExcel_Writer_Excel5($obj);
            $objWriter->save('php://output');
            exit;
        } elseif ($pathInfo['extension'] == 'xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $pathInfo['filename'] . '.xlsx');
            header('Cache-Control: max-age=1');
            $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
            $objWriter->save('php://output');
            exit;
        }
    }
}
